﻿Imports System.Data.OleDb
Imports System.IO


Public Class ConnectionDAO
    Public connectdto As ConnectionDTO
    Public command As OleDbCommand
    Public adapter As OleDbDataAdapter
    Public _connection As OleDbConnection


    Public Sub New()
        connectdto = New ConnectionDTO()
        GetConnectionString()
        command = New OleDbCommand()
        adapter = New OleDbDataAdapter()
    End Sub

    Private Sub GetConnectionString()
        _connection = New OleDbConnection()
        Dim sr As StreamReader
        Try
            If Not File.Exists(Application.StartupPath & "\Banhangconnect.udl") Then
                MessageBox.Show("Không tìm thấy file cấu hình kết nối cơ sỡ dữ liệu .")
                Exit Sub
            End If
            sr = File.OpenText(Application.StartupPath & "\Banhangconnect.udl")

            sr.ReadLine()
            sr.ReadLine()

            _connection.ConnectionString = sr.ReadLine

            'Sữa lại thành biến toàn cục sau khi làm form chính

            Dim arrConnectionString As String()
            Dim sDestination As String
            Dim arrParameter As String()
            arrConnectionString = _connection.ConnectionString.Split(";")
            For Each sDestination In arrConnectionString
                arrParameter = sDestination.Split("=")
                Select Case arrParameter(0)
                    Case "Provider"
                        connectdto.ProviderDatabase = arrParameter(1)
                    Case "Password"
                        connectdto.PasswordDatabase = arrParameter(1)
                    Case "User ID"
                        connectdto.UserDatabase = arrParameter(1)
                    Case "Initial Catalog"
                        connectdto.DatabaseName = arrParameter(1)
                    Case "Data Source"
                        connectdto.ServerName = arrParameter(1)
                End Select
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub OpenConnection()
        If _connection.State = ConnectionState.Closed Or _connection.State = ConnectionState.Broken Then
            _connection.Open()
        End If
    End Sub

    Private Sub CloseConnection()
        _connection.Close()
    End Sub


    Public Function ExecuteNoneQuery(ByVal _query As String) As Boolean '
        'command.Parameters.Clear()
        OpenConnection()
        Try
            command.Connection = _connection
            command.CommandType = CommandType.Text
            command.CommandText = _query
            'command.Parameters.AddRange(arrParameter)
            'adapter.InsertCommand = command
            command.ExecuteNonQuery()
            CloseConnection()
        Catch ex As Exception
            objErrorLog.SaveError(ex.Message, "", "", "")
            Return False
        End Try
        Return True
    End Function

    Public Function ExecuteStoreprocedureNoneReturn(ByVal _storeName As String, ByVal arrParameter As OleDbParameter()) As Boolean
        OpenConnection()
        command.Parameters.Clear()
        Try
            command.Connection = _connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = _storeName
            If Not arrParameter Is Nothing Then
                command.Parameters.AddRange(arrParameter)
            End If

            'adapter.InsertCommand = command
            command.ExecuteNonQuery()

            CloseConnection()

        Catch ex As Exception
            objErrorLog.SaveError(ex.Message, "", "", "")
            Return False
        End Try
        Return True
    End Function

    Public Function ExecuteStoreprocedure(ByVal _storeName As String, ByVal arrParameter As OleDbParameter()) As DataSet
        OpenConnection()
        command.Parameters.Clear()
        Dim dt As New DataTable()
        Dim ds As New DataSet


        Try
            command.Connection = _connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = _storeName

            If Not arrParameter Is Nothing Then
                command.Parameters.AddRange(arrParameter)
            End If

            'adapter.InsertCommand = command
            adapter = New OleDbDataAdapter(command)
            'adapter.SelectCommand = command
            'command.ExecuteNonQuery()
            adapter.Fill(ds)
            'dt = ds.Tables(0)

            CloseConnection()
        Catch ex As Exception
            objErrorLog.SaveError(ex.Message, "", "", "")
        End Try

        Return ds
    End Function

    Public Function ExecuteSelectQuery(ByVal _query As String) As DataSet
        OpenConnection()
        'command.Parameters.Clear()
        Dim dt As New DataTable()
        Dim ds As New DataSet()
        Try
            command.Connection = _connection
            command.CommandType = CommandType.Text
            command.CommandText = _query
            'command.Parameters.AddRange(arrParameter)
            'command.ExecuteNonQuery()
            adapter.SelectCommand = command
            adapter.Fill(ds)
            'dt = ds.Tables(0)

            CloseConnection()

        Catch ex As Exception
            objErrorLog.SaveError(ex.Message, "", "", "")
        End Try

        Return ds
    End Function

End Class

